/*
 * Powered By [leyvi]
 * Since 2014 - 2015
 */

var memberModel = require('../model/Member');
var db = require('../../utils/db/mysqlDb');
var common = require('../../utils/commonUtils');
var Promise = require('promise');

var memberDb = {

};

var defaultSelectColumn = ' id,username,password,phone,email,head_img_url,fans_num,create_time,update_time,bak1,bak2,bak3,bak4,bak5';

var defaultTable = 'member';
//getById
memberDb.getById = function(id){
    return new Promise(function(fulfill, reject){
        var sql = 'select ' + defaultSelectColumn + ' from ' + defaultTable+ ' where 1=1 ';
        if(typeof id === "object"){
            sql+='and id= ' + db.escape(id.id);
        }else{
            sql+=' and id= ' + db.escape(id);
        }
        db.query(sql).then(function(rows){
            if(rows[0]){
                fulfill(memberModel.dbConvertModel(rows[0]));
            }else{
                fulfill(null);
            }             
        }, function(err){
            reject(err);
        });
    });
};

//save
memberDb.save = function(obj, connection){
    return db.save('insert into ' + defaultTable+ '  set ?',
             memberModel.modelConvertDb(obj), connection);
};

//update
memberDb.update = function(obj, connection){
    var sql = 'update ' + defaultTable+ '  set ';
    if(typeof obj.username != "undefined"){
        sql+='username=:username,';
    }
    if(typeof obj.password != "undefined"){
        sql+='password=:password,';
    }
    if(typeof obj.phone != "undefined"){
        sql+='phone=:phone,';
    }
    if(typeof obj.email != "undefined"){
        sql+='email=:email,';
    }
    if(typeof obj.headImgUrl != "undefined"){
        sql+='head_img_url=:headImgUrl,';
    }
    if(common.isNotBlank(obj.fansNum)){
        sql+='fans_num=:fansNum,';
    }
    if(common.isNotBlank(obj.createTime)){
        sql+='create_time=:createTime,';
    }
    if(common.isNotBlank(obj.updateTime)){
        sql+='update_time=:updateTime,';
    }
    if(common.isNotBlank(obj.bak1)){
        sql+='bak1=:bak1,';
    }
    if(common.isNotBlank(obj.bak2)){
        sql+='bak2=:bak2,';
    }
    if(common.isNotBlank(obj.bak3)){
        sql+='bak3=:bak3,';
    }
    if(typeof obj.bak4 != "undefined"){
        sql+='bak4=:bak4,';
    }
    if(typeof obj.bak5 != "undefined"){
        sql+='bak5=:bak5,';
    }
    sql+='where ';
        sql+='id=:id ';
        sql = sql.replace(',where', '  where');
    return db.update(sql, obj, connection);
};

//delete
memberDb.delete = function(id, connection){
    var sql = 'delete from ' + defaultTable+ '  where 1=1 ';
    if(typeof id === "object"){
        sql+=' and id= ' + db.escape(id.id);
    }else{
        sql+=' and id= ' + db.escape(id);
    }
    return db.update(sql, {}, connection);
};

//查询条件
var selectParams = function(params){
    var sql = '';
    if(params && typeof params.id != "undefined"){
        sql+=' and  id=' + db.escape(params.id);
    }
    if(params && typeof params.username != "undefined"){
        sql+=' and  username=' + db.escape(params.username);
    }
    if(params && typeof params.password != "undefined"){
        sql+=' and  password=' + db.escape(params.password);
    }
    if(params && typeof params.phone != "undefined"){
        sql+=' and  phone=' + db.escape(params.phone);
    }
    if(params && typeof params.email != "undefined"){
        sql+=' and  email=' + db.escape(params.email);
    }
    if(params && typeof params.headImgUrl != "undefined"){
        sql+=' and  head_img_url=' + db.escape(params.headImgUrl);
    }
    if(params && typeof params.fansNum != "undefined"){
        sql+=' and  fans_num=' + db.escape(params.fansNum);
    }
    if(params && typeof params.createTime != "undefined"){
        sql+=' and  create_time=' + db.escape(params.createTime);
    }
    if(params && typeof params.updateTime != "undefined"){
        sql+=' and  update_time=' + db.escape(params.updateTime);
    }
    if(params && typeof params.bak1 != "undefined"){
        sql+=' and  bak1=' + db.escape(params.bak1);
    }
    if(params && typeof params.bak2 != "undefined"){
        sql+=' and  bak2=' + db.escape(params.bak2);
    }
    if(params && typeof params.bak3 != "undefined"){
        sql+=' and  bak3=' + db.escape(params.bak3);
    }
    if(params && typeof params.bak4 != "undefined"){
        sql+=' and  bak4=' + db.escape(params.bak4);
    }
    if(params && typeof params.bak5 != "undefined"){
        sql+=' and  bak5=' + db.escape(params.bak5);
    }
    return sql;
};


//count
memberDb.count = function(params){
    return new Promise(function(fulfill, reject){
        var sql = 'select count(*) as total from ' + defaultTable;
        sql += ' where 1=1 ';
        sql += selectParams(params);
        db.query(sql).then(function(rows){
            fulfill(rows[0].total);
        }, function(err){
            reject(err);
        });
    });
};

//page
memberDb.getByPage = function(params, pageNo, pageSize, orderby){
    return new Promise(function(fulfill, reject){
        var sql = 'select ' + defaultSelectColumn + ' from ' + defaultTable;
        sql+=' where 1=1 ';
        sql += selectParams(params);
        sql+=' order by ' + orderby;
        if(pageNo <= 0){
            pageNo = 1;
        }
        pageNo = common.isNumber(pageNo) ? pageNo : 1;
        pageSize = common.isNumber(pageSize) ? pageSize : 10;
        sql += ' limit ';
        sql += (pageNo - 1) * pageSize;
        sql +=',';
        sql +=pageSize;
       
        db.query(sql).then(function(rows){
            var array = new Array();
            for(var p in rows){
                if(rows[p])array.push(memberModel.dbConvertModel(rows[p]));
            }
            fulfill(array);
        }, function(err){
            reject(err);
        });
    });
};

//page
memberDb.getLikeMemberByPage = function(params, pageNo, pageSize){
    return new Promise(function(fulfill, reject){
        var sql = 'select ' + defaultSelectColumn + ' from ' + '(select m.* from member m join likes l on l.member_id = m.id';
        sql += ' where l.status = 0 and l.article_id = '+db.escape(params.articleId);
        sql += ') t ';
        if(pageNo <= 0){
            pageNo = 1;
        }
        pageNo = common.isNumber(pageNo) ? pageNo : 1;
        pageSize = common.isNumber(pageSize) ? pageSize : 10;
        sql += ' limit ';
        sql += (pageNo - 1) * pageSize;
        sql +=',';
        sql +=pageSize;

        db.query(sql).then(function(rows){
            var array = new Array();
            for(var p in rows){
                if(rows[p])array.push(memberModel.dbConvertModel(rows[p]));
            }
            fulfill(array);
        }, function(err){
            reject(err);
        });
    });
};

memberDb.getByParams = function(params, orderby){
    return new Promise(function(fulfill, reject){
        var sql = 'select ' + defaultSelectColumn + ' from ' + defaultTable;
        sql+=' where 1=1 ';
        sql += selectParams(params);
        sql+=' order by ' + orderby;
        db.query(sql).then(function(rows){
            var array = new Array();
            for(var p in rows){
                if(rows[p])array.push(memberModel.dbConvertModel(rows[p]));
            }
            fulfill(array);
        }, function(err){
            reject(err);
        });
    });
};
memberDb.getAllUnLookCommentNum = function (memberId) {
    return new Promise(function (fulfill,reject) {
        var sql = ' select count(*)  as total '+
            ' from (select article_id,create_time,content '+
            ' from comment '+
            ' where article_id '+
            ' in '+
            ' (SELECT id from article where member_id = '+db.escape(memberId)+' and bak1 = 0))c '+
            ' left join member_log l '+
            ' on c.article_id = l.target_id and l.type = 0 '+
            ' where '+
            ' (l.log_time is null or l.log_time < c.create_time ) ';
        db.query(sql).then(function(rows){
            fulfill(rows[0].total);
        }, function(err){
            reject(err);
        });
    })
}

memberDb.getAllAttentionUnLooKNum = function (memberId) {
    return new Promise(function (fulfill,reject) {
        var sql = ' select sum(total) as total'+
                    ' from (select * from (select count(*) as total from'+
                    ' (select member_id,title,create_time'+
                    ' from'+
                    ' article'+
                    ' where member_id'+
                    ' in('+
                    ' SELECT article_id'+
                    ' as member_id'+
                    ' from attention'+
                    ' where member_id = '+db.escape(memberId)+' '+
                    ' and status = 1))a'+
                    ' LEFT JOIN member_log l '+
                    ' on a.member_id = l.target_id  and l.type = 1 '+
                    ' where (l.member_id is null or l.member_id = '+db.escape(memberId)+' )'+
                    ' and (l.log_time is null or l.log_time < a.create_time))cre'+
                    ' union all'+
                    ' select *'+
                    ' from (select count(*) as total from(select * from collect'+
                    ' where member_id'+
                    ' in'+
                    ' (SELECT article_id'+
                    ' as member_id'+
                    ' from attention'+
                    ' where member_id = '+db.escape(memberId)+' '+
                    ' and status = 1))c'+
                    ' LEFT JOIN member_log l'+
                    ' on c.member_id = l.target_id  and l.type = 2 '+
                    ' where (l.member_id is null or l.member_id = '+db.escape(memberId)+' )'+
                    ' and (l.log_time is null or l.log_time < c.create_time))coll)t ';
        db.query(sql).then(function(rows){
            fulfill(rows[0].total);
        }, function(err){
            reject(err);
        });
    })
}

memberDb.getAttentionUnLooKNum = function (myMemberId,attentionMemberId) {
    return new Promise(function (fulfill,reject) {
        var sql = ' select sum(total) as total'+
            ' from (select * from (select count(*) as total from'+
            ' (select member_id,title,create_time'+
            ' from'+
            ' article'+
            ' where member_id =  '+db.escape(attentionMemberId)+''+
            ' )a'+
            ' LEFT JOIN member_log l'+
            ' on a.member_id = l.target_id and l.type = 1 '+
            ' where (l.member_id is null or l.member_id = '+db.escape(myMemberId)+' )'+
            ' and (l.log_time is null or l.log_time < a.create_time))cre'+
            ' union all'+
            ' select *'+
            ' from (select count(*) as total from(select * from collect'+
            ' where member_id =  '+db.escape(attentionMemberId)+''+
            ' )c'+
            ' LEFT JOIN member_log l'+
            ' on c.member_id = l.target_id  and l.type = 2 '+
            ' where (l.member_id is null or l.member_id = '+db.escape(myMemberId)+' )'+
            ' and (l.log_time is null or l.log_time < c.create_time))coll)t ';
        db.query(sql).then(function(rows){
            fulfill(rows[0].total);
        }, function(err){
            reject(err);
        });
    })
}
memberDb.getUnLookCollectNum = function (myMemberId,attentionMemberId) {
    return new Promise(function (fulfill,reject) {
        var sql =
            ' select *'+
            ' from (select count(*) as total from(select * from collect'+
            ' where member_id =  '+db.escape(attentionMemberId)+''+
            ' )c'+
            ' LEFT JOIN member_log l'+
            ' on c.member_id = l.target_id  and l.type = 2 '+
            ' where (l.member_id is null or l.member_id = '+db.escape(myMemberId)+' )'+
            ' and (l.log_time is null or l.log_time < c.create_time))coll ';
        db.query(sql).then(function(rows){
            fulfill(rows[0].total);
        }, function(err){
            reject(err);
        });
    })
}
memberDb.db = db;
module.exports = memberDb;